Modern application design methodology, particularly for applications with graphical user interfaces, emphasizes the user being able to perform work in as many independent sessions (often corresponding to windows on a graphical display device) as are convenient, where the changes made in a session are not permanent until the user chooses to make them so.
Relational databases-use the concept of a transaction, which allows changes to be made, without being permanent. In known databases an open transaction may limit or prevent other users from accessing information, involved in that transaction. Transactions require a database connection each, for which a license charge may apply.
It is thus desirable to keep information for each session in temporary storage, and save all changes from temporary storage to the database at one time, protected by a transaction only for the duration of the save. Furthermore, the temporary storage is most useful if managed so that it appears to contain (a possibly modified version of) all information in the database, reading and caching database information when requests are made that cannot be satisfied from information already available in the cache.
Modem database schema design methodology emphasizes avoiding redundant information storage, by normalizing the database schema. Normalized schemas result in information, that users or business logic consider as a whole, being stored in the schema as a row in each of several database tables. Known relational databases allow retrieval from multiple joined tables, but modification only to single tables. For the developer to provide an easily understood user interface, and efficiently implement business logic, a means of accessing any row referenced by a singleton relationship, must be provided, with reasonable default behaviour when a singleton relationship has an empty reference. A means of accessing sets of rows, referenced by set relationships, is also a requirement.
Known systems cache database data in the form of objects, where each object maps to multiple rows in a database. An example is disclosed in U.S. Pat. No. 5,615,362. The object cache approach has severe problems where two objects partially overlap on a particular row. Modifications which would map to the overlapped row, and should be visible in both objects, are only visible in the object which was directly modified.
Business rules and business processes (collectively referred to as business logic), necessary for the application, may be implemented in the relational database, in the application presentation code, in a middle layer connected by network (physical three-tier), or in a middle layer within the application (logical three-tier). Best practice involves separating the application presentation code from the business logic, to simplify construction and maintenance of the two. Unfortunately development and debugging tools and facilities, available for relational database logic, are poor. This must be programmed using the database vendor's proprietary extensions to SQL, which language was never designed for programming. A middle layer, within the application but separate from the presentation code, can be implemented using powerful development and debugging tools, and does not require separate cache facilities for a physically separate middle layer.
Relationships between rows will change as certain fields of the rows are changed. Application developers must manually detect when relationships have or may have changed, and manually re-query the database, or cache, to update the user interface.
Separate caches involved in separate sessions for the same user, present a possible problem in that different and inconsistent changes may be in the different caches. Much work has gone into some systems in attempting to provide a single coherent cache for the entire application. The inconsistency problem between different caches within one application, is very similar to the inconsistency problem between users on different computers. Both of these problems may be avoided, and concurrent usage maximized, by the use of `optimistic locking`, where each row for modification is identified to the database by all original field values, thus ensuring that other users or cache saves have not modified the row since it was read.
Known relational databases enforce referential integrity, where database operations which would result in references to non-existent rows are prevented. When cached rows are saved to the database, the order in which the rows are saved must be controlled to avoid violating referential integrity. When dealing with two related rows, the order in which they must be inserted is generally the opposite of the order in which they must be deleted.
Developers of database applications spend a large percentage of application development time dealing with the above issues.